package com.huweilong.tingshu.repository;

import com.huweilong.tingshu.data_object.TidToCountDO;
import com.huweilong.tingshu.util.DBUtil;
import com.huweilong.tingshu.util.Log;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author 是阿秋啊
 * @date 2022/07/21 14:43
 **/
public class RelationRepo {

    @SneakyThrows
    public List<TidToCountDO> selectCountByTidListGroupByTidList(List<Integer> tidList) {
        String sqlFormat = "select tid, count(*) from relations where tid in (%s) group by tid order by tid";
        StringBuilder sb = new StringBuilder();
        for (Integer tid : tidList) {
            sb.append(tid).append(", ");
        }
        sb.delete(sb.length()-2,sb.length());
        String sql = String.format(sqlFormat,sb.toString());

        List<TidToCountDO> list = new ArrayList<>();
        try(Connection c = DBUtil.connection()) {
            try(PreparedStatement ps = c.prepareStatement(sql)) {
                Log.println("执行SQL:"+ps);
                try(ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        TidToCountDO tidToCountDO = new TidToCountDO(
                                rs.getInt(1),
                                rs.getInt(2)
                        );
                        list.add(tidToCountDO);
                    }
                }
            }
        }
        return list;
    }

    @SneakyThrows
    public List<Integer> selectListByAid(int aid) {
        List<Integer> list = new ArrayList<>();
        String sql = "select tid from relations where aid = ? order by rid";
        try(Connection c = DBUtil.connection()){
            try(PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,aid);
                Log.println("执行SQL:"+ps);
                try(ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        list.add(rs.getInt("tid"));
                    }
                    return list;
                }
            }
        }
    }

    @SneakyThrows
    public void insert(int aid,List<Integer> tidList) {
        String sqlFormat = "insert into relations (aid, tid) values %s";
        String s = tidList.stream().map(tid -> String.format("(%d, %d)", aid, tid)).collect(Collectors.joining(","));
        String sql = String.format(sqlFormat,s);
        Log.println("执行SQL:"+sql);
        try(Connection c = DBUtil.connection()){
            try(PreparedStatement ps = c.prepareStatement(sql)){
                ps.executeUpdate();
            }
        }
    }
}